package com.cci.kangdao.dao;

import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.MapUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mybatis.spring.annotation.MapperScan;

@MapperScan
public interface AllServiceStationDao {
	/**
	 * 根据UserID和服务渠道id查询该区域服务经理下的服务站ID
	 */
	List<Map<String,Object>> getServiceStationId(Map<String, Object> map);

	/**
	 * 根据服务站名字模糊搜索服务站信息
	 */
	List<Map<String,Object>> getSearchServiceStationId(Map<String, Object> map);

	/**
	 * 区域工程师所在区域下的所有服务站
	 */
	List<Map<String,Object>> getAllServiceStationInCSH(Long orderId);

	/**
	 * 区域工程师所在区域下的所有服务站
	 */
	List<Map<String, Object>> getAllServiceStationInCSHNew(@Param("userPhone") String userPhone);


	@Select("select * from RecommendThreeStationForDBU_T where OrderId = #{orderId} and Flag = 0")
	Map<String,Object> getRecommendThreeStationForDBUByOrderId(@Param("orderId") long orderId);



	//DBU区域工程师获取服务站列表
	//只查询服务站类型为服务商的服务站(1代表服务商    2代表CSH    3代表分公司)
	@Select("select top(#{pageSize}) c.ID as serviceStationId,geography::Point(c.PositionLat, c.PositionLon, 4326).STDistance(geography::Point(#{PositionLat},#{PositionLon}, 4326))/1000 as distance,c.Address as address,c.StationName as serviceSite,c.PositionLon as longitude,\n" +
			"c.PositionLat as latitude,c.Telephone as telephone,d.SSLevel,d.SSType\n" +
			"from PositionUserServiceStation_T a\n" +
			"inner join PositionUser_T b on a.PositionUserID = b.ID \n" +
			"inner join ServiceStation_T c on a.ServiceStationID = c.ID\n" +
			"inner join SSRepair_T d on c.ID = d.ServiceStationID \n" +
			"where b.UserPhone =#{userPhone} and a.Flag = 0 and b.Flag = 0 and d.CompanyID = 1 and d.Flag = 0 and c.StationName like concat('%',#{stationName},'%') and c.ServiceStationCategory = 1\n" +
			"and c.ID not in \n" +
			"(\n" +
			"select top ((#{pageNo}-1)*#{pageSize}) pusst.ServiceStationID from PositionUserServiceStation_T pusst \n" +
			"inner join PositionUser_T put on pusst.PositionUserID = put.ID \n" +
			"inner join ServiceStation_T sst on pusst.ServiceStationID = sst.ID \n" +
			"where put.UserPhone=#{userPhone} and pusst.Flag=0 and put.Flag=0 and sst.ServiceStationCategory=1\n" +
			"order by case when pusst.ServiceStationID in\n" +
			"(\n" +
			"select FirstStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			"union\n" +
			"select SecondStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			"union\n" +
			"select ThirdStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			")  \n" +
			"then 0 else 1 end,\n" +
			"geography::Point(sst.PositionLat, sst.PositionLon, 4326).STDistance(geography::Point(#{PositionLat},#{PositionLon}, 4326))/1000\n" +
			")\n" +
			"order by case when c.ID in\n" +
			"(\n" +
			"select FirstStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			"union\n" +
			"select SecondStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			"union\n" +
			"select ThirdStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}\n" +
			")  \n" +
			"then 0 else 1 end,\n" +
			"case when c.ID = (select FirstStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}) then 0\n" +
			"else 1 end,\n" +
			"case when c.ID = (select SecondStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}) then 1\n" +
			"else 2 end,\n" +
			"case when c.ID = (select ThirdStationId from RecommendThreeStationForDBU_T where OrderId = #{orderId}) then 2\n" +
			"else 3 end,\n" +
			"geography::Point(c.PositionLat, c.PositionLon, 4326).STDistance(geography::Point(#{PositionLat},#{PositionLon}, 4326))/1000")
	List<Map<String,Object>> getServiceStationListByAreaEngineer(@Param("orderId") long orderId,@Param("userPhone") String userPhone,
																 @Param("PositionLat") float PositionLat,@Param("PositionLon")float PositionLon,
																 @Param("pageNo") int pageNo,@Param("pageSize") int pageSize,
																 @Param("stationName")String stationName);

	//根据xcec获取该服务渠道下所有服务站
	@Select("select top (#{pageSize}) * from(\n" +
            "select * from (\n" +
            "select b.ID as serviceStationId,b.StationName as serviceSite,a.HotlineTel as telephone,b.Address as address,b.PositionLon as longitude,b.PositionLat as latitude,\n" +
            "a.SSLevel,a.SSType,\n" +
            "geography::Point(b.PositionLat, b.PositionLon, 4326).STDistance(geography::Point(#{PositionLat},#{PositionLon}, 4326))/1000 as distance\n" +
            "from SSRepair_T a\n" +
            "inner join ServiceStation_T b on a.ServiceStationID =b.ID\n" +
            "where a.CompanyID = #{companyId} and a.Flag = 0 and b.StationName like   concat('%',#{stationName},'%')  ) r) t\n" +
            "where t.serviceStationId not in (\n" +
            "select top ((#{pageNo}-1)*#{pageSize}) t2.serviceStationId from\n" +
            "(select * from (\n" +
            "select b.ID as serviceStationId,b.StationName as serviceSite,a.HotlineTel as telephone,b.Address as address,b.PositionLon as longitude,b.PositionLat as latitude,\n" +
            "a.SSLevel,a.SSType,\n" +
            "geography::Point(b.PositionLat, b.PositionLon, 4326).STDistance(geography::Point(#{PositionLat},#{PositionLon}, 4326))/1000 as distance\n" +
            "from SSRepair_T a\n" +
            "inner join ServiceStation_T b on a.ServiceStationID =b.ID\n" +
            "where a.CompanyID = #{companyId} and a.Flag = 0 and b.StationName like  concat('%',#{stationName},'%')   ) r) t2 order by t2.distance\n" +
            ")\n" +
            "order by t.distance")
	List<Map<String,Object>> getXCECServiceStationList(@Param("pageNo") int pageNo,@Param("pageSize") int pageSize,
													   @Param("PositionLat") float PositionLat,@Param("PositionLon")float PositionLon,
													   @Param("stationName")String stationName,@Param("companyId")long companyId);

	//根据服务站ids获取服务站详情
	List<Map<String, Object>> getServiceStationDetail(@Param("serviceStationIds")List<Long> serviceStationIds, @Param("PositionLat") float PositionLat, @Param("PositionLon")float PositionLon, @Param("stationName")String stationName);

    //根据服务站ids获取服务站集合
    List<Map<String, Object>> getServiceStationNoLon(@Param("serviceStationIds") List<Long> serviceStationIds, @Param("stationName") String stationName);

	/**
	* 根据工单id获取推荐服务站详情
	* */
	List<Map<String, Object>> getRecommendedServiceStationDetail(@Param("orderId") Long orderId,@Param("userPhone") String userPhone,@Param("PositionLat") float PositionLat,@Param("PositionLon")float PositionLon,@Param("stationName")String stationName);


}
